﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class InvestFactBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);



        private string formatDate(string value)
        {
            string tmp = null;
            string[] tmp2 = null;
            tmp2 = value.Split('/');
            tmp = tmp2[0] + "/" + tmp2[1] + "/" + tmp2[2];   

            return tmp;
        }


        public void insert(string inv_name, string inv_ref_no,string inv_date,string remarks,string crd_descr,int crd_seq,int stat_seq,int created_by)
        {
            try
            {



                string tmpDate;
                tmpDate = formatDate(inv_date);  

                DateTime invDate = Convert.ToDateTime(tmpDate);

                dbManager.Open();
                dbManager.CreateParameters(10);
                dbManager.AddParameters(0, "@inv_name", inv_name);
                dbManager.AddParameters(1, "@inv_ref_no", inv_ref_no);
                dbManager.AddParameters(2, "@inv_date",invDate );
                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@crd_descr", crd_descr);
                dbManager.AddParameters(5, "@crd_seq",crd_seq);
                dbManager.AddParameters(6, "@stat_seq", stat_seq);
                dbManager.AddParameters(7, "@record_status",SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@created_by", created_by);
                dbManager.AddParameters(9, "@created_when", DateTime.Today);


            


                string sql;

                sql = " insert into "
                + " hrm_dc_investigations "
                + " (inv_type_seq,"
                + " inv_name,"
                + " inv_ref_no,"
                + " inv_date,"
                + " remarks,"
                + " crd_seq,"
                + " crd_descr,"
                + " stat_seq,"
                + " record_status,"
                + " created_by,"
                + " created_when)"

                + " values "
                + " (1,"
                + " @inv_name,"
                + " @inv_ref_no,"
                + " @inv_date,"
                + " @remarks,"
                + " @crd_seq,"
                + " @crd_descr,"
                + " @stat_seq,"
                + " @record_status,"
                + " @created_by,"
                + " @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }

        
        public DataTable Select()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
                + " hrm_dc_investigations.*,"
                + " hrm_dc_statuses.stat_seq, "
                + " hrm_dc_statuses.stat_name, "
                + " hrm_dc_investigation_types.inv_type_name, "
                + " hrm_dc_investigation_types.inv_type_seq"
                + " FROM "
                + " hrm_dc_investigations "
                + " Inner Join hrm_dc_investigation_types ON hrm_dc_investigations.inv_type_seq = hrm_dc_investigation_types.inv_type_seq"
                + " Inner Join hrm_dc_statuses ON hrm_dc_statuses.stat_seq = hrm_dc_investigations.stat_seq"
                + " where hrm_dc_investigations.inv_type_seq = 1 "
                + " and hrm_dc_investigations.record_status = 'U'";                    

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public DataTable SelectStatus()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_dc_statuses where record_status = '" + SystemInfo._ActiveRecordStatus  + "'"  ;
             
                DataSet ds = new DataSet();
                DataTable dt;
                                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();  
            }

        }


        public DataTable SelectComboCrime()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_dc_crime_dtls where record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public void Update(int dataKey, string inv_name, string inv_ref_no, string inv_date, string remarks, string crd_descr, int crd_seq, int stat_seq, int updated_by)
        {
            try
            {


                string tmpDate;
                tmpDate = formatDate(inv_date);


                DateTime invDate = Convert.ToDateTime(tmpDate);  
                dbManager.Open();
                dbManager.CreateParameters(11);
                dbManager.AddParameters(0, "@inv_name", inv_name);
                dbManager.AddParameters(1, "@inv_ref_no", inv_ref_no);
                dbManager.AddParameters(2, "@inv_date",invDate );
                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@crd_descr", crd_descr);
                dbManager.AddParameters(5, "@crd_seq", crd_seq);
                dbManager.AddParameters(6, "@stat_seq", stat_seq);
                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@updated_by", updated_by);
                dbManager.AddParameters(9, "@updated_when", DateTime.Today);
                dbManager.AddParameters(10, "@dataKey", dataKey);


                string sql;

                sql = " update "
                + " hrm_dc_investigations "
                + " set "
                + " inv_name=@inv_name,"
                + " inv_ref_no=@inv_ref_no,"
                + " inv_date=@inv_date,"
                + " remarks=@remarks,"
                + " crd_seq=@crd_seq,"
                + " crd_descr=@crd_descr,"
                + " stat_seq=@stat_seq,"
                + " record_status=@record_status,"
                + " updated_by=@updated_by, "
                + " updated_when=@updated_when"
                + " where inv_seq=@dataKey";
               



                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }

 
        public void Delete(int inv_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@inv_seq", inv_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
               
                sql = " update hrm_dc_investigations set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
    
                + " where inv_seq = @inv_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                
                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }



        /////////////////////     POPUP  /////////////////////////


        public DataTable SelecDetails(int inv_seq)
        {
            try
            {
                dbManager.Open();
                string sql;



                sql = " SELECT "
                    + " hrm_dc_investigation_dtls.inv_dtls_seq,"
                    + " mas_name_prefixes.prefix_name,"
                    + " hrm_ps_employees.first_name,"
                    + " hrm_ps_employees.last_name,"
                    + " hrm_mp_positions.pos_name,"
                    + " hrm_mp_classes.class_name,"
                    + " cast(concat(mas_name_prefixes.prefix_name, hrm_ps_employees.first_name, ' ', hrm_ps_employees.last_name) as char) as full_name,"
                    + " isNULL(hrm_ps_employees.working_job_site_seq,hrm_mp_job_sites3.job_site_seq) AS working_job_site_seq,"
                    + " isNULL(hrm_mp_job_sites2.job_site_name,hrm_mp_job_sites3.job_site_name) AS working_job_site_name,"
                    + " hrm_mp_job_sites3.job_site_seq,"
                    + " hrm_mp_job_sites3.job_site_name,"
                    + " hrm_mp_positions.pos_ref"
                    + " FROM hrm_dc_investigation_dtls"
                    + " left Join hrm_ps_employees ON hrm_dc_investigation_dtls.inv_emp_seq = hrm_ps_employees.emp_seq" // change inner join to left join
                    + " left Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq"
                    + " left Join hrm_mp_classes ON hrm_ps_employees.class_seq = hrm_mp_classes.class_seq"
                    + " left Join mas_name_prefixes ON hrm_ps_employees.name_prefix_seq = mas_name_prefixes.name_prefix_seq"
                    + " left Join hrm_mp_job_sites AS hrm_mp_job_sites2 ON hrm_ps_employees.working_job_site_seq = hrm_mp_job_sites2.job_site_seq"
                    + " left Join hrm_mp_job_sites AS hrm_mp_job_sites3 ON hrm_mp_positions.job_site_seq = hrm_mp_job_sites3.job_site_seq"
                    //+ " Inner Join hrm_dc_investigation_dtls ON hrm_dc_investigation_dtls.inv_emp_seq = hrm_ps_employees.emp_seq"
                    + " Inner Join hrm_dc_investigations ON hrm_dc_investigations.inv_seq = hrm_dc_investigation_dtls.inv_seq"
                    + " where hrm_dc_investigation_dtls.record_status = 'U'"
                    //+ " and hrm_dc_investigations.inv_type_seq=1"
                    + " and hrm_dc_investigation_dtls.inv_seq = " + inv_seq ;



                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectEmpType()
        {

            try
            {
                dbManager.Open();
                string sql;

                sql = " SELECT * From HRM_PS_EMPLOYEE_TYPES ";
                 
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public void InsertDtls(int inv_seq,int emp_seq,string created_by)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@inv_seq", inv_seq);
                dbManager.AddParameters(1, "@inv_emp_seq", emp_seq);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@created_by", created_by);
                dbManager.AddParameters(4, "@created_when", DateTime.Today);


                string sql;

                sql = " insert into "
                + " hrm_dc_investigation_dtls "
                + " (inv_seq,"
                + " inv_emp_seq,"
                + " record_status,"
                + " created_by,"
                + " created_when)"

                + " values "
                + " (@inv_seq,"
                + " @inv_emp_seq,"
                + " @record_status,"
                + " @created_by,"
                + " @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }
        }

        public void DeleteDtls(int seq, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@inv_dtls_seq", seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;

               sql = " update hrm_dc_investigation_dtls set "
              + " record_status=@record_status,"
              + " updated_by = @updated_by,"
              + " updated_when = @updated_when"
              + " where inv_dtls_seq = @inv_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }








     

   
    }
}
